package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.entity.tables.records.UserRoleRecord
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.familyexpenseapi.jooq.RecordHelper
import org.jooq.DSLContext
import org.springframework.stereotype.Service

@Service
class JoinAccountGroupRequestService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val userService: UserService,
) {
    /**
     * Counts received requests by receiver user id.
     *
     * @param receiverUserId: String of receiver user id.
     * @return: Count of requests.
     */
    fun countReceived(receiverUserId: String): Int {
        return dslContextWrapper.count(
            JOIN_ACCOUNT_GROUP_REQUEST,
            JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverUserId),
        )
    }

    /**
     * Queries pending requests for receiver.
     */
    fun queryReceiving(receiverUserId: String, pageSize: Int, pageIndex: Int): List<Map<String, Any?>> {
        // Queries receiver's the request list.
        // Define table aliases
        val ownerAlias = USER.`as`("owner")
        val senderAlias = USER.`as`("sender")

        return dslContext
            .select(
                JOIN_ACCOUNT_GROUP_REQUEST.ID.`as`("id"),
                ownerAlias.USERNAME.`as`("ownerUsername"),
                ownerAlias.FULL_NAME.`as`("ownerFullName"),
                ownerAlias.AVATAR_FILENAME.`as`("ownerAvatarFilename"),
                senderAlias.USERNAME.`as`("senderUsername"),
                senderAlias.FULL_NAME.`as`("senderFullName"),
                JOIN_ACCOUNT_GROUP_REQUEST.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(JOIN_ACCOUNT_GROUP_REQUEST)
            .join(ownerAlias).on(JOIN_ACCOUNT_GROUP_REQUEST.OWNER_ID.eq(ownerAlias.ID))
            .join(senderAlias).on(JOIN_ACCOUNT_GROUP_REQUEST.SENDER_ID.eq(senderAlias.ID))
            .where(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverUserId))
            .orderBy(JOIN_ACCOUNT_GROUP_REQUEST.LAST_UPDATED.desc())
            .limit(pageSize)
            .offset(pageSize * pageIndex)
            .fetch()
            .map { record ->
                mapOf(
                    "id" to record["id"],
                    "owner" to mapOf(
                        "username" to record["ownerUsername"].toString(),
                        "fullName" to record["ownerFullName"],
                        "avatarUrl" to userService.getAvatarUrl(record["ownerAvatarFilename"] as String?),
                    ),
                    "sender" to mapOf(
                        "username" to record["senderUsername"].toString(),
                        "fullName" to record["senderFullName"],
                    ),
                    "lastUpdated" to record["lastUpdated"],
                )
            }
    }

    /**
     * Adds new request or updates existing one.
     *
     * @param accountGroupId: The string of account group id.
     * @param senderId:       The string of the sender's user id.
     * @param receiverId:     The string of the receiver's user id.
     * @param roles:          List of role ids assigned to the receiver.
     */
    fun addOrUpdate(accountGroupId: String, senderId: String, receiverId: String, roles: List<String>) {
        // Queries any existing request even if sender is not the current user.
        // We just need to make sure only one request is available at the same time.
        val exists = dslContextWrapper.exists(
            JOIN_ACCOUNT_GROUP_REQUEST, JOIN_ACCOUNT_GROUP_REQUEST.ACCOUNT_GROUP_ID.eq(accountGroupId)
                .and(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverId))
        )

        val joinedRoles = roles.joinToString(",")

        if (!exists) {
            val ownerId = dslContext.selectFrom(ACCOUNT_GROUP_USER)
                .where(
                    ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
                )
                .fetchOne(ACCOUNT_GROUP_USER.USER_ID)

            dslContext.newRecord(JOIN_ACCOUNT_GROUP_REQUEST).apply {
                this.accountGroupId = accountGroupId
                this.ownerId = ownerId
                this.senderId = senderId
                this.receiverId = receiverId
                this.roles = joinedRoles
                insert()
            }

        } else {
            // Updates roles just in case they have been changed.
            dslContext.update(JOIN_ACCOUNT_GROUP_REQUEST)
                .set(JOIN_ACCOUNT_GROUP_REQUEST.ROLES, joinedRoles)
                .set(JOIN_ACCOUNT_GROUP_REQUEST.SENDER_ID, senderId)
                .where(
                    JOIN_ACCOUNT_GROUP_REQUEST.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverId))
                )
                .execute()
        }
    }

    /**
     * Request receiver accepts this request.
     *
     * @param id:         The string of request id.
     * @param receiverId: The string of the user as receiver.
     */
    fun accept(id: String, receiverId: String) {
        val data = dslContext.selectFrom(JOIN_ACCOUNT_GROUP_REQUEST)
            .where(
                JOIN_ACCOUNT_GROUP_REQUEST.ID.eq(id)
                    .and(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverId))
            )
            .fetchOne()

        if (data != null) {
            val roleIds = data.roles.split(",")

            // Saves relationship between user and account group.
            dslContext.newRecord(ACCOUNT_GROUP_USER).run {
                accountGroupId = data.accountGroupId
                userId = data.receiverId
                isAccountGroupCreator = false
                insert()
            }

            // Saves all user roles.
            val userRoleRecords = mutableListOf<UserRoleRecord>()

            for (roleId in roleIds) {
                dslContext.newRecord(USER_ROLE).run {
                    RecordHelper.setCommonFields(this)
                    accountGroupId = data.accountGroupId
                    userId = data.receiverId
                    this.roleId = roleId
                    userRoleRecords.add(this)
                }
            }

            if (userRoleRecords.isNotEmpty()) {
                dslContext.batchInsert(userRoleRecords).execute()
            }

            // Deletes the completed request.
            deleteReceiving(id, receiverId)
        }
    }

    /**
     * Request receiver rejects this request.
     *
     * @param id:         The string of request id.
     * @param receiverId: The string of the user as receiver.
     */
    fun reject(id: String, receiverId: String) {
        // Deletes the completed request.
        deleteReceiving(id, receiverId)
    }

    /**
     * Deletes request based on its id and receiver id.
     *
     * @param id: The string of request id.
     */
    private fun deleteReceiving(id: String, receiverId: String) {
        dslContext.deleteFrom(JOIN_ACCOUNT_GROUP_REQUEST)
            .where(
                JOIN_ACCOUNT_GROUP_REQUEST.ID.eq(id)
                    .and(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(receiverId))
            )
            .execute()
    }
}